import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_就业地分布_省外生源(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    就业地分布-省外生源 - 指标计算函数
    
    ## 指标说明
    该指标用于计算省外生源学生的就业地分布情况，包括：
    1. 省内就业人数占比
    2. 回生源地就业人数占比
    3. 其他省份就业人数占比
    数据来源于学生客观表，通过分析生源地和就业地关系计算得出。

    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "calcVals": [
                    {
                        "val": [
                            {
                                "key": "省内就业占比",
                                "val": "0.3333"
                            },
                            {
                                "key": "回生源地就业占比",
                                "val": "0.3333"
                            },
                            {
                                "key": "其他省份就业占比",
                                "val": "0.3333"
                            }
                        ]
                    }
                ]
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 就业地分布-省外生源, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM dp_njc.client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询问卷信息
        questionnaire_sql = f"""
        SELECT id, dy_target 
        FROM wt_template_customer 
        WHERE id IN ({','.join(['%s'] * len(questionnaire_ids))})
        """
        questionnaires = db.fetchall(questionnaire_sql, tuple(questionnaire_ids))
        if not questionnaires:
            raise ValueError(f"未找到问卷ID集合={questionnaire_ids}的配置信息")
        
        logger.info(f"查询到问卷信息: {questionnaires}")

        # 4. 执行三段SQL查询
        # 4.1 计算其他省份就业人数
        sql_other_province = f"""
        SELECT count(*) as other_province_count 
        FROM dim_client_target_baseinfo_student_calc_{item_year} t2 
        WHERE t2.shard_tb_key='{shard_tb_key}' 
        AND session in ('{item_year}') 
        AND t2.birth_around in ('生源省外','省外生源') 
        AND t2.job_around in ('就业省外','省外就业') 
        AND t2.job_province!=t2.birth_province 
        AND t2.grad_dest_merge != '待就业' 
        AND t2.grad_dest_merge != '暂不就业'
        """
        other_province_result = db.fetchone(sql_other_province)
        other_province_count = other_province_result['other_province_count'] if other_province_result else 0

        # 4.2 计算回生源地就业人数
        sql_hometown = f"""
        SELECT count(*) as hometown_count 
        FROM dim_client_target_baseinfo_student_calc_{item_year} t2 
        WHERE t2.shard_tb_key='{shard_tb_key}' 
        AND session in ('{item_year}') 
        AND t2.birth_around in ('生源省外','省外生源') 
        AND t2.job_around in ('就业省外','省外就业') 
        AND t2.job_province=t2.birth_province 
        AND t2.grad_dest_merge != '待就业' 
        AND t2.grad_dest_merge != '暂不就业'
        """
        hometown_result = db.fetchone(sql_hometown)
        hometown_count = hometown_result['hometown_count'] if hometown_result else 0

        # 4.3 计算省内就业人数
        sql_local = f"""
        SELECT count(*) as local_count 
        FROM dim_client_target_baseinfo_student_calc_{item_year} t2 
        WHERE t2.shard_tb_key='{shard_tb_key}' 
        AND session in ('{item_year}') 
        AND t2.birth_around in ('生源省外','省外生源') 
        AND t2.job_around in ('就业省内','省内就业') 
        AND t2.grad_dest_merge != '待就业' 
        AND t2.grad_dest_merge != '暂不就业'
        """
        local_result = db.fetchone(sql_local)
        local_count = local_result['local_count'] if local_result else 0

        logger.info(f"查询结果: 省内就业={local_count}, 回生源地就业={hometown_count}, 其他省份就业={other_province_count}")

        # 5. 计算占比
        total = local_count + hometown_count + other_province_count
        if total == 0:
            raise ValueError("未找到符合条件的记录，无法计算占比")

        local_ratio = round(local_count / total, 4)
        hometown_ratio = round(hometown_count / total, 4)
        other_province_ratio = round(other_province_count / total, 4)

        # 6. 构建返回数据
        result_data = [
            {"key": "省内就业占比", "val": f"{local_ratio:.4f}"},
            {"key": "回生源地就业占比", "val": f"{hometown_ratio:.4f}"},
            {"key": "其他省份就业占比", "val": f"{other_province_ratio:.4f}"}
        ]

        logger.info(f"指标 '就业地分布-省外生源' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": [{
                "calcVals": [{
                    "val": result_data
                }]
            }]
        }

    except Exception as e:
        logger.error(f"计算指标 '就业地分布-省外生源' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 就业地分布-省外生源",
            "code": 500,
            "error": str(e)
        }